package ece.edt;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.ObjectInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import ece.edt.exceptions.HyperPlanningException;

public class HyperPlanningDB extends HyperPlanning {
	private static final Logger log = Logger.getLogger(HyperPlanningDB.class);
	private Connection connection;
	private Statement statement;
	
	private static HyperPlanningDB hyperPlanningDB;
	
	public static HyperPlanningDB getInstance(CASClient client, Connection c) throws HyperPlanningException{	
		if(hyperPlanningDB == null){
			hyperPlanningDB = new HyperPlanningDB(client, c);
		}
		
		return hyperPlanningDB;
	}

	public static HyperPlanningDB getInstance(CASClient client, String dbUser, String dbPassword, String dbUrl, String dbName) throws HyperPlanningException{
		if(hyperPlanningDB == null){
			hyperPlanningDB = new HyperPlanningDB(client, dbUser, dbPassword, dbUrl, dbName);
		}
		
		return hyperPlanningDB;
	}
	
	protected HyperPlanningDB(CASClient client, Connection c) throws HyperPlanningException {
		super(client);
		this.connection = c;
		
		try {
			statement = connection.createStatement();
		} catch (SQLException e) {
			log.error(e, e);
		}
	}
	
	protected HyperPlanningDB(CASClient client, String dbUser, String dbPassword, String dbUrl, String dbName) throws HyperPlanningException {
		super(client);
		try {
			Class.forName("com.mysql.jdbc.Driver");
			connection = DriverManager.getConnection(dbUrl + dbName, dbUser, dbPassword);
			statement = connection.createStatement();
			
		} catch (ClassNotFoundException e) {
			log.error(e, e);
		} catch (SQLException e) {
			log.error(e, e);
		}
	}

	public List<User> getUsers(String beginWith, Ressource type){
		List<User> users = new ArrayList<User>();

		try {
			ResultSet rs = statement.executeQuery("SELECT * FROM various WHERE name='users_refresh' AND date + INTERVAL 1 DAY > NOW()");
			try {
				if (!rs.first()) {
					initConnection();
					statement.executeUpdate("TRUNCATE TABLE users");
					statement.executeUpdate("TRUNCATE TABLE schedules");

					users = super.getUsers("*", Ressource.CLASSE);
					users.addAll(super.getUsers("*", Ressource.PROFESSEUR));

					StringBuilder sql = new StringBuilder();
					sql.append("INSERT INTO users VALUES \n");
					for (User u : users) {
						sql.append("(" + u.getID() + ", \"" + u.getName().replace("\"", "") + "\", '" + u.getType().name() + "'), \n");
					}
					statement.executeUpdate(sql.substring(0, sql.length() - 3));

					int rowUpdated = statement.executeUpdate("UPDATE various SET date=NOW() WHERE name='users_refresh'");

					if (rowUpdated == 0) {
						statement.executeUpdate("INSERT INTO various VALUES ('users_refresh', '', NOW())");
					}
				}
			} catch (HyperPlanningException e) {
				log.error(e, e);
			} finally {
				rs.close();
			}

			ResultSet rs2 = statement.executeQuery("SELECT * FROM users WHERE name LIKE \"" + beginWith + "%\" AND type='" + type.name() + "'");
			try {
				users.clear();
				while (rs2.next()) {
					users.add(new User(rs2.getString("name"), rs2.getString("id"), rs2.getString("type")));
				}
			} finally {
				rs2.close();
			}
		} catch (SQLException e) {
			log.error(e, e);
		}	
		
		return users;
	}
	
	/**
	 * @see ece.edt.HyperPlanning#grab(ece.edt.HyperPlanning.User, boolean)
	 */
	public Schedule grab(User user, boolean getWeeksNonValidated) throws HyperPlanningException {

		String sql = "SELECT data FROM schedules WHERE user_id=? AND official=? AND date + INTERVAL 12 HOUR > NOW()";
		try {
			PreparedStatement preparedStatement = connection.prepareStatement(sql);

			try {
				preparedStatement.setString(1, user.getID());
				preparedStatement.setBoolean(2, !getWeeksNonValidated);

				ResultSet rs = preparedStatement.executeQuery();
				try {
					Schedule schedule;
					
					if (rs.first()) {
						InputStream bais = new ByteArrayInputStream(rs.getBytes("data"));
						
						try {
							ObjectInputStream in = new ObjectInputStream(bais);
							return (Schedule) ((ObjectInputStream) in).readObject();
							
						} catch (IOException e) {
							log.error(e, e);
						} catch (ClassNotFoundException e) {
							log.error(e, e);
						}
					} else {
						schedule = super.grab(user, getWeeksNonValidated);

						sql = "UPDATE schedules SET date=NOW(), data=? WHERE user_id=? AND official=?";
						PreparedStatement preparedStatement2 = connection.prepareStatement(sql);
						int rowUpdated;
						try {
							preparedStatement2.setObject(1, schedule);
							preparedStatement2.setString(2, user.getID());
							preparedStatement2.setBoolean(3, !getWeeksNonValidated);
	
							rowUpdated = preparedStatement2.executeUpdate();
						} finally {
							preparedStatement2.close();
						}
						
						if (rowUpdated == 0) {
							String sql2 = "INSERT INTO schedules (date, user_id, official, data) VALUES (NOW(), ?, ?, ?)";
							PreparedStatement preparedStatement3 = connection.prepareStatement(sql2);

							try {
								preparedStatement3.setString(1, user.getID());
								preparedStatement3.setBoolean(2, !getWeeksNonValidated);
								preparedStatement3.setObject(3, schedule);
								preparedStatement3.executeUpdate();
							} finally {
								preparedStatement3.close();
							}
						}
					
						return schedule;
					}
					
				} finally {
					rs.close();
				}
			} finally {
				preparedStatement.close();
			}
		} catch (SQLException e) {
			log.error(e, e);
		}

		return null;
	}
	
	public void closeConnection() throws SQLException {
		try {
			statement.close();
		} finally {
			connection.close();
		}
	}
}
